﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using AutoBackupDatabase.DBConection;
using AutoBackupDatabase.ENT;


namespace AutoBackupDatabase.DAL
{
    public class USERACCOUNTDAL
    {
        private DbConnection conn;

        /// <constructor>
        /// Constructor UserDAO
        /// </constructor>
        public USERACCOUNTDAL()
        {
            conn = new DbConnection();
        }

        public List<USERACCOUNTENT> GET_USERACCOUNT_ALL()
        {
            string query = string.Format("exec GET_USERACCOUNT_ALL ");
            DataTable ds = conn.executeGetQuery(query);
            List<USERACCOUNTENT> List = new List<USERACCOUNTENT>();
            try
            {
              
                foreach (DataRow item in ds.Rows)
                {
                    USERACCOUNTENT USERACCOUNTent = new USERACCOUNTENT();
                    USERACCOUNTent.DATECREATE = DateTime.Parse(item["DATECREATE"].ToString());
                    USERACCOUNTent.DOB = DateTime.Parse(item["DOB"].ToString());
                    USERACCOUNTent.EMAIL = item["EMAIL"].ToString();
                    USERACCOUNTent.EMPLOYERID = item["EMPLOYERID"].ToString();
                    USERACCOUNTent.FIRSTNAME = item["FIRSTNAME"].ToString();
                    USERACCOUNTent.FULLNAME = item["FULLNAME"].ToString();
                    USERACCOUNTent.GROUPUSERCODE = item["GROUPUSERCODE"].ToString();
                    USERACCOUNTent.HOMEPHONE = item["HOMEPHONE"].ToString();
                    USERACCOUNTent.IDCARD = item["IDCARD"].ToString();
                    USERACCOUNTent.GETISADMIN = bool.Parse(item["ISADMIN"].ToString());
                    USERACCOUNTent.LASTNAME = item["LASTNAME"].ToString();
                    USERACCOUNTent.LASTUPDATE = DateTime.Parse(item["LASTUPDATE"].ToString());
                    USERACCOUNTent.MIDDLENAME = item["MIDDLENAME"].ToString();
                    USERACCOUNTent.MOBILEPHONE = item["MOBILEPHONE"].ToString();
                    USERACCOUNTent.USERNAME = item["USERNAME"].ToString();

                    List.Add(USERACCOUNTent);
                }
                return List;
            }
            catch (Exception)
            {

                return List;
            }
            
            
        }

        public bool INSERTUSERACCOUNT(USERACCOUNTENT ent)
        {
            string query = "exec  INSERTUSERACCOUNT @USERNAME,@PASSWORD ,@FIRSTNAME,@MIDDLENAME ,@LASTNAME ,@FULLNAME ,@DOB ,@EMAIL ,@IDCARD  ,@ISADMIN ,@MOBILEPHONE ,@HOMEPHONE ,@GROUPUSERCODE ,@EMPLOYERID";
            SqlParameter[] sqlParameters = new SqlParameter[14];
            sqlParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(ent.USERNAME);
            sqlParameters[1] = new SqlParameter("@PASSWORD", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(ent.PASSWORD);
            sqlParameters[2] = new SqlParameter("@FIRSTNAME", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(ent.FIRSTNAME);
            sqlParameters[3] = new SqlParameter("@MIDDLENAME", SqlDbType.NVarChar);
            sqlParameters[3].Value = Convert.ToString(ent.MIDDLENAME);
            sqlParameters[4] = new SqlParameter("@LASTNAME", SqlDbType.NVarChar);
            sqlParameters[4].Value = Convert.ToString(ent.LASTNAME);
            sqlParameters[5] = new SqlParameter("@FULLNAME", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(ent.FULLNAME);
            sqlParameters[6] = new SqlParameter("@DOB", SqlDbType.DateTime);
            sqlParameters[6].Value = Convert.ToDateTime(ent.DOB);
            sqlParameters[7] = new SqlParameter("@EMAIL", SqlDbType.NVarChar);
            sqlParameters[7].Value = Convert.ToString(ent.EMAIL);
            sqlParameters[8] = new SqlParameter("@IDCARD", SqlDbType.VarChar);
            sqlParameters[8].Value = Convert.ToString(ent.IDCARD);
            sqlParameters[9] = new SqlParameter("@ISADMIN", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToInt64(ent.ISADMIN);
            sqlParameters[10] = new SqlParameter("@MOBILEPHONE", SqlDbType.VarChar);
            sqlParameters[10].Value = Convert.ToString(ent.MOBILEPHONE);
            sqlParameters[11] = new SqlParameter("@HOMEPHONE", SqlDbType.VarChar);
            sqlParameters[11].Value = Convert.ToString(ent.HOMEPHONE);
            sqlParameters[12] = new SqlParameter("@GROUPUSERCODE", SqlDbType.Int);
            sqlParameters[12].Value = Convert.ToInt64(ent.GROUPUSERCODE);
            sqlParameters[13] = new SqlParameter("@EMPLOYERID", SqlDbType.Int);
            sqlParameters[13].Value = Convert.ToInt64(ent.EMPLOYERID);
   
            return conn.executeInsertQuery(query, sqlParameters);
        }
        public bool UPDATEUSERACOUNTBYID(USERACCOUNTENT ent)
        {
            string query = "exec  UPDATEUSERACOUNTBYID @USERNAME,@PASSWORD ,@FIRSTNAME,@MIDDLENAME ,@LASTNAME ,@FULLNAME ,@DOB ,@EMAIL ,@IDCARD  ,@ISADMIN ,@MOBILEPHONE ,@HOMEPHONE ,@GROUPUSERCODE ,@EMPLOYERID";
            SqlParameter[] sqlParameters = new SqlParameter[14];
            sqlParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(ent.USERNAME);
            sqlParameters[1] = new SqlParameter("@PASSWORD", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(ent.PASSWORD);
            sqlParameters[2] = new SqlParameter("@FIRSTNAME", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(ent.FIRSTNAME);
            sqlParameters[3] = new SqlParameter("@MIDDLENAME", SqlDbType.NVarChar);
            sqlParameters[3].Value = Convert.ToString(ent.MIDDLENAME);
            sqlParameters[4] = new SqlParameter("@LASTNAME", SqlDbType.NVarChar);
            sqlParameters[4].Value = Convert.ToString(ent.LASTNAME);
            sqlParameters[5] = new SqlParameter("@FULLNAME", SqlDbType.NVarChar);
            sqlParameters[5].Value = Convert.ToString(ent.FULLNAME);
            sqlParameters[6] = new SqlParameter("@DOB", SqlDbType.DateTime);
            sqlParameters[6].Value = Convert.ToDateTime(ent.DOB);
            sqlParameters[7] = new SqlParameter("@EMAIL", SqlDbType.NVarChar);
            sqlParameters[7].Value = Convert.ToString(ent.EMAIL);
            sqlParameters[8] = new SqlParameter("@IDCARD", SqlDbType.VarChar);
            sqlParameters[8].Value = Convert.ToString(ent.IDCARD);
            sqlParameters[9] = new SqlParameter("@ISADMIN", SqlDbType.Int);
            sqlParameters[9].Value = Convert.ToInt64(ent.ISADMIN);
            sqlParameters[10] = new SqlParameter("@MOBILEPHONE", SqlDbType.VarChar);
            sqlParameters[10].Value = Convert.ToString(ent.MOBILEPHONE);
            sqlParameters[11] = new SqlParameter("@HOMEPHONE", SqlDbType.VarChar);
            sqlParameters[11].Value = Convert.ToString(ent.HOMEPHONE);
            sqlParameters[12] = new SqlParameter("@GROUPUSERCODE", SqlDbType.Int);
            sqlParameters[12].Value = Convert.ToInt64(ent.GROUPUSERCODE);
            sqlParameters[13] = new SqlParameter("@EMPLOYERID", SqlDbType.Int);
            sqlParameters[13].Value = Convert.ToInt64(ent.EMPLOYERID);
            return conn.executeUpdateQuery(query, sqlParameters);
        }
        public bool DELTELEBYUSERACCOUNTBYUSERACCOUNT(USERACCOUNTENT ent)
        {
            string query = "exec  DELTELEBYUSERACCOUNTBYUSERACCOUNT @USERNAME";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(ent.USERNAME);
            return conn.executeDeleteQuery(query, sqlParameters);
        }
        

        public USERACCOUNTENT Login(USERACCOUNTENT ent)
        {
            string query = string.Format("exec LOGIN_CHECK @USERNAME, @PASSWORD");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(ent.USERNAME);
            sqlParameters[1] = new SqlParameter("@PASSWORD", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(ent.PASSWORD);

            DataTable ds = conn.executeSelectQuery(query, sqlParameters);
         
            try
            {
                if (ds.Rows.Count > 0)
                {
                    USERACCOUNTENT USERACCOUNTent = new USERACCOUNTENT();
                    foreach (DataRow item in ds.Rows)
                    {
                        
                        USERACCOUNTent.DATECREATE = DateTime.Parse(item["DATECREATE"].ToString());
                        USERACCOUNTent.DOB = DateTime.Parse(item["DOB"].ToString());
                        USERACCOUNTent.EMAIL = item["EMAIL"].ToString();
                        USERACCOUNTent.EMPLOYERID = item["EMPLOYERID"].ToString();
                        USERACCOUNTent.FIRSTNAME = item["FIRSTNAME"].ToString();
                        USERACCOUNTent.FULLNAME = item["FULLNAME"].ToString();
                        USERACCOUNTent.GROUPUSERCODE = item["GROUPUSERCODE"].ToString();
                        USERACCOUNTent.HOMEPHONE = item["HOMEPHONE"].ToString();
                        USERACCOUNTent.IDCARD = item["IDCARD"].ToString();
                        USERACCOUNTent.GETISADMIN = bool.Parse(item["ISADMIN"].ToString());
                        USERACCOUNTent.LASTNAME = item["LASTNAME"].ToString();
                        USERACCOUNTent.LASTUPDATE = DateTime.Parse(item["LASTUPDATE"].ToString());
                        USERACCOUNTent.MIDDLENAME = item["MIDDLENAME"].ToString();
                        USERACCOUNTent.MOBILEPHONE = item["MOBILEPHONE"].ToString();
                        USERACCOUNTent.USERNAME = item["USERNAME"].ToString();


                    }
                    return USERACCOUNTent;
                }

                return null;
            } 
            catch (Exception)
            {

                return new USERACCOUNTENT();
            }


        }

        public bool CHANGEPASSWORD(string userName, string passOld, string passNew)
        {
            string query = "exec  CHANGEPASSWORD @USERNAME,@PASSWORDOLD,@PASSWORDNEW";
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(userName);
            sqlParameters[1] = new SqlParameter("@PASSWORDOLD", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(passOld);
            sqlParameters[2] = new SqlParameter("@PASSWORDNEW", SqlDbType.VarChar);
            sqlParameters[2].Value = Convert.ToString(passNew);
      
            
            return conn.executeUpdateQuery(query, sqlParameters);
        }

        public bool RESETPASSWORD(string userName, string passNew)
        {
            string query = "exec  RESETPASSWORD @USERNAME,@PASSWORDNEW";
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@USERNAME", SqlDbType.VarChar);
            sqlParameters[0].Value = Convert.ToString(userName);
            sqlParameters[1] = new SqlParameter("@PASSWORDNEW", SqlDbType.VarChar);
            sqlParameters[1].Value = Convert.ToString(passNew);

            return conn.executeUpdateQuery(query, sqlParameters);
        }

    }
}
